package com.cjbdi.service;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.cjbdi.util.NumUtil;

/**
 * shzlcbrzsService
 */
@Service
@Transactional
public class ShzlService{
	@Autowired  
    @PersistenceContext  
    private EntityManager entityManager;// 注入EntityManager实例 
	@Autowired
	BaseService baseService;
	//概述（和去年同比） 
	public List getGsList(String kind, String type, String date, String fybm,String title,int multiple){ 
    	String sql = "select date,jbfy,sfzs_czrk.fyjc as fy,cnum,pnum,(cnum/pnum)*"+multiple+" as result from sfzs_result_shzl,sfzs_czrk where sfzs_czrk.\"year\"='"+date+"' and sfzs_czrk.fybm = sfzs_result_shzl.jbfy and title='"+title+"' and type='"+type+"' and date='"+date+"' and kind='"+kind+"' and jbfy ='"+fybm+"'";
		Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
	//附表表格（区县本身）
    public List getFbQxSelfList(String type, String date, String fybm, String title){  
    	String sql = "select replace(fymc,'人民法院','法院') as fyjc,glnum,totalnum,result from sfzs_result_shzl where  title='" +title +"' and type='"+type+"' and sfzs_result_shzl.date ='"+date+"' and kind='辖' and jbfy ='"+fybm+"' order by result desc";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
	//高院附表
	public List getFbZjList(String type, String date, String fybm,String title){ 
		StringBuffer sql = new StringBuffer();
		if (fybm.equals("ZZ0")) {
			sql.append("SELECT REPLACE\r\n" + 
					"	( sfzs_czrk.fyjc, '法院', '' ) AS fyjc,\r\n" + 
					"	( CASE WHEN yearc IS NULL THEN 0 ELSE yearc END ) AS yearc,\r\n" + 
					"	sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num ELSE 0 END)) AS yearp,\r\n" + 
					"	( CASE WHEN lastyearc IS NULL THEN 0 ELSE lastyearc END ) AS lastyearc,\r\n" + 
					"	sum((CASE WHEN sfzs_czrk.year = '"+(Integer.parseInt(date)-1)+"' THEN num ELSE 0 END)) AS lastyearp \r\n" + 
					"FROM\r\n" + 
					"	sfzs_czrk\r\n" + 
					"	LEFT JOIN (\r\n" + 
					"SELECT\r\n" + 
					"	jbfy,\r\n" + 
					"	sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN cnum ELSE 0 END ) AS yearc,\r\n" + 
					"	sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN pnum ELSE 0 END ) AS yearp,\r\n" + 
					"	sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN cnum ELSE 0 END ) AS lastyearc,\r\n" + 
					"	sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN pnum ELSE 0 END ) AS lastyearp \r\n" + 
					"FROM\r\n" + 
					"	sfzs_result_shzl\r\n" + 
					"WHERE\r\n" + 
					"  type = '"+type+"' \r\n" + 
					"	AND kind = '高' \r\n" + 
					"	AND title = '"+title+"' \r\n" + 
					"GROUP BY\r\n" + 
					"	jbfy\r\n" + 
					"	\r\n" + 
					"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
					"WHERE\r\n" + 
					"sfzs_czrk.fybm<>'X00' and \r\n" + 
					"substring( sfzs_czrk.fybm FROM 2 FOR 2 ) = '00' and\r\n" + 
					"sfzs_czrk.year in('"+date+"','"+(Integer.parseInt(date)-1)+"')"+
					"GROUP BY sfzs_czrk.fyjc,t.yearc,t.lastyearc");
			
		}else if (fybm.equals("100") || fybm.equals("200") || fybm.equals("M00") || fybm.equals("900")) {// 直辖市
			sql.append("SELECT REPLACE\r\n" + 
	    			"	( sfzs_czrk.fyjc, '法院', '' ) AS fyjc,\r\n" + 
	    			"	( CASE WHEN yearc IS NULL THEN 0 ELSE yearc END ) AS yearc,\r\n" + 
	    			"	sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num ELSE 0 END)) AS yearp,\r\n" + 
	    			"	( CASE WHEN lastyearc IS NULL THEN 0 ELSE lastyearc END ) AS lastyearc,\r\n" + 
	    			"	sum((CASE WHEN sfzs_czrk.year = '"+(Integer.parseInt(date)-1)+"' THEN num ELSE 0 END)) AS lastyearp \r\n" + 
	    			"FROM\r\n" + 
	    			"	sfzs_czrk\r\n" + 
	    			"	LEFT JOIN (\r\n" + 
	    			"SELECT\r\n" + 
	    			"	jbfy,\r\n" + 
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN cnum ELSE 0 END ) AS yearc,\r\n" + 
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN pnum ELSE 0 END ) AS yearp,\r\n" + 
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN cnum ELSE 0 END ) AS lastyearc,\r\n" + 
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN pnum ELSE 0 END ) AS lastyearp \r\n" + 
	    			"FROM\r\n" + 
	    			"	sfzs_result_shzl\r\n" + 
	    			"WHERE\r\n" + 
	    			"  type = '"+type+"' \r\n" + 
	    			"	AND kind = '辖' \r\n" + 
	    			"	AND title = '"+title+"' \r\n" + 
	    			"GROUP BY\r\n" + 
	    			"	jbfy\r\n" + 
	    			"	\r\n" + 
	    			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
	    			"WHERE\r\n" + 
	    			"sfzs_czrk.fybm LIKE '"+fybm.substring(0, 1)+"%' and sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' and substring(sfzs_czrk.fybm FROM 3 FOR 1 ) <>'0' \r\n" + 
	    			"and sfzs_czrk.year in('"+date+"','"+(Integer.parseInt(date)-1)+"')" + 
	    			"GROUP BY sfzs_czrk.fyjc,t.yearc,t.lastyearc");
		}else {
			sql.append("SELECT REPLACE\r\n" + 
	    			"	( sfzs_czrk.fyjc, '法院', '' ) AS fyjc,\r\n" + 
	    			"	( CASE WHEN yearc IS NULL THEN 0 ELSE yearc END ) AS yearc,\r\n");
	    			if(title.equals("lyjf")) {
	    				sql.append("( CASE WHEN (yearp IS NULL OR yearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num_lyrk ELSE 0 END)) ELSE yearp END ) AS yearp,");
	    			}else if(title.equals("jrzp")) {
	    				sql.append("( CASE WHEN (yearp IS NULL OR yearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num_jrtl ELSE 0 END)) ELSE yearp END ) AS yearp,");
	    			}else if(title.equals("dljt")) {
	    				sql.append("( CASE WHEN (yearp IS NULL OR yearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num_myqc ELSE 0 END)) ELSE yearp END ) AS yearp,");
	    			}else {
	    				sql.append("( CASE WHEN (yearp IS NULL OR yearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num ELSE 0 END)) ELSE yearp END ) AS yearp,");
	    			}
	    	sql.append("( CASE WHEN lastyearc IS NULL THEN 0 ELSE lastyearc END ) AS lastyearc,\r\n");
	    	if(title.equals("lyjf")) {
	    		sql.append("( CASE WHEN (lastyearp IS NULL OR lastyearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num_lyrk ELSE 0 END)) ELSE lastyearp END ) AS lastyearp \r\n");
			}else if(title.equals("jrzp")) {
				sql.append("( CASE WHEN (lastyearp IS NULL OR lastyearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num_jrtl ELSE 0 END)) ELSE lastyearp END ) AS lastyearp \r\n");
			}else if(title.equals("dljt")) {
				sql.append("( CASE WHEN (lastyearp IS NULL OR lastyearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num_myqc ELSE 0 END)) ELSE lastyearp END ) AS lastyearp \r\n");
			}else {
				sql.append("( CASE WHEN (lastyearp IS NULL OR lastyearp=0) THEN sum((CASE WHEN sfzs_czrk.year = '"+date+"' THEN num ELSE 0 END)) ELSE lastyearp END ) AS lastyearp \r\n");
			}
	    	sql.append("FROM\r\n" + 
	    			"	sfzs_czrk\r\n" + 
	    			"	LEFT JOIN (\r\n" + 
	    			"SELECT\r\n" + 
	    			"	jbfy,\r\n" + 
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN cnum ELSE 0 END ) AS yearc,\r\n" + 
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN pnum ELSE 0 END ) AS yearp,"+
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN cnum ELSE 0 END ) AS lastyearc,\r\n" + 
	    			"	sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN pnum ELSE 0 END ) AS lastyearp \r\n" + 
	    			"FROM\r\n" + 
	    			"	sfzs_result_shzl\r\n" + 
	    			"WHERE\r\n" + 
	    			"  type = '"+type+"' \r\n" + 
	    			"	AND kind = '中' \r\n" + 
	    			"	AND title = '"+title+"' \r\n" + 
	    			"	AND sfzs_result_shzl.date in('"+date+"','"+(Integer.parseInt(date)-1)+"')" + 
	    			"GROUP BY\r\n" + 
	    			"	jbfy\r\n" + 
	    			"	\r\n" + 
	    			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
	    			"WHERE\r\n" + 
	    			"sfzs_czrk.fybm LIKE '"+fybm.substring(0, 1)+"%0' and sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' \r\n" + 
	    			"and sfzs_czrk.year in('"+date+"','"+(Integer.parseInt(date)-1)+"')" + 
	    			"GROUP BY sfzs_czrk.fyjc,t.yearc,t.lastyearc,yearp,lastyearp order by yearc");
		}
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //全国排名表格
    public List getQgTableList(String type, String date, String title,int multiple, boolean isQuanguo){ 
    	String sql ="";
    	if(isQuanguo) {
    		if(type.equals("year")) {
    			sql ="SELECT jbfy,fymc as fy," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+(Integer.parseInt(date)-1)+"' then (cnum/pnum)*"+multiple+" else 0 end ) as NUMERIC),2) as lastyearzs," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+date+"' then (cnum/pnum)*"+multiple+" else 0 end) as NUMERIC),2) as yearzs," + 
            			"(Round(cast(sum(case when sfzs_result_shzl.date='"+date+"' then (cnum/pnum)*"+multiple+" else 0 end) as NUMERIC),2) - Round(cast(sum(case when sfzs_result_shzl.date='"+(Integer.parseInt(date)-1)+"' then (cnum/pnum)*"+multiple+" else 0 end ) as NUMERIC),2)) as cz from sfzs_result_shzl where fymc='全国' and type='"+type+"' and kind ='高' and title='"+title+"' GROUP BY jbfy,fymc";
    		}else {
    			sql ="SELECT\r\n" + 
    					"	jbfy,\r\n" + 
    					"	fy,\r\n" + 
    					"	lastyearzs,\r\n" + 
    					"	hbzs,\r\n" + 
    					"	yearzs,\r\n" + 
    					"	(yearzs-lastyearzs) as cz,\r\n" + 
    					"	(yearzs-hbzs) as hbcz\r\n" + 
    					"	from(SELECT jbfy,fymc as fy," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then (cnum/pnum)*"+multiple+" else 0 end ) as NUMERIC),2) as lastyearzs," + 
            			"Round(cast(SUM ( CASE WHEN sfzs_result_shzl.DATE = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS hbzs," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+date+"' and type='"+type+"' then (cnum/pnum)*"+multiple+" else 0 end) as NUMERIC),2) as yearzs" + 
            			" from sfzs_result_shzl where fymc='全国' and kind ='高' and title='"+title+"' GROUP BY jbfy,fymc)t";
    		}
    		
    	}else {
    		if(type.equals("year")) {
    			sql = "select c_fbdm as jbfy,fy,lastyearzs,yearzs,(yearzs-lastyearzs) as cz,rank() over(order by yearzs) as rank from(select c_fbdm,c_mc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs from t_fypz left join (SELECT jbfy," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+(Integer.parseInt(date)-1)+"' then (cnum/pnum)*"+multiple+" else 0 end ) as NUMERIC),2) as lastyearzs," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+date+"' then (cnum/pnum)*"+multiple+" else 0 end) as NUMERIC),2) as yearzs" + 
            			" from sfzs_result_shzl where jbfy<>'X00' and fymc!='全国' and type='"+type+"' and kind ='高' and title='"+title+"' GROUP BY jbfy,fymc)\r\n" + 
            					"t on t_fypz.c_fbdm = t.jbfy  where substring(t_fypz.c_fbdm from 2 for 2)='00' and t_fypz.c_fbdm<>'000' and t_fypz.c_fbdm<>'X00' and t_fypz.c_fbdm<>'W00' order by yearzs,lastyearzs,fy)t1";
    		}else {
    			sql = "select c_fbdm as jbfy,fy,lastyearzs,\r\n" + 
    					"	hbzs,\r\n" + 
    					"	yearzs,\r\n" + 
    					"	(yearzs-lastyearzs) as cz,\r\n" + 
    					"	(yearzs-hbzs) as hbcz,rank() over(order by yearzs) as rank from(select c_fbdm,c_mc as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,(CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs from t_fypz left join (SELECT jbfy," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+(Integer.parseInt(date)-1)+"' and type='"+type+"' then (cnum/pnum)*"+multiple+" else 0 end ) as NUMERIC),2) as lastyearzs," + 
            			"Round(cast(SUM ( CASE WHEN sfzs_result_shzl.DATE = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS hbzs," + 
            			"Round(cast(sum(case when sfzs_result_shzl.date='"+date+"' and type='"+type+"' then (cnum/pnum)*"+multiple+" else 0 end) as NUMERIC),2) as yearzs" + 
            			" from sfzs_result_shzl where jbfy<>'X00' and fymc!='全国' and kind ='高' and title='"+title+"' GROUP BY jbfy,fymc)\r\n" + 
            					"t on t_fypz.c_fbdm = t.jbfy  where substring(t_fypz.c_fbdm from 2 for 2)='00' and t_fypz.c_fbdm<>'000' and t_fypz.c_fbdm<>'X00' and t_fypz.c_fbdm<>'W00' order by yearzs,lastyearzs,fy)t1";
    		}
    		
    	}
    	Query query = entityManager.createNativeQuery(sql);  
    	query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    public List getZjTableList(String type, String date, String fybm,String title,String kind,int multiple){  
    	StringBuffer sql = new StringBuffer();
    	if(type.equals("year")) {
    		sql.append("select jbfy, fy,lastyearzs,yearzs,(yearzs-lastyearzs) as cz,rank() over(order by yearzs) as rank from(select sfzs_czrk.fybm as jbfy,replace(fyjc,'法院','') as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs from sfzs_czrk left join " + 
        			"(SELECT" + 
        			"	jbfy," + 
        			"	Round(cast(SUM ( CASE WHEN sfzs_result_shzl.DATE = '"+(Integer.parseInt(date)-1)+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS lastyearzs," + 
        			"	Round(cast(SUM ( CASE WHEN sfzs_result_shzl.DATE = '"+date+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS yearzs " + 
        			"FROM" + 
        			"	sfzs_result_shzl " + 
        			"WHERE" + 
        			"	jbfy LIKE'"+fybm.substring(0, 1)+"%' " + 
        			"	AND TYPE = '"+type+"' " + 
        			"	AND DATE IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) " + 
        			"	AND kind = '"+kind+"' " + 
        			"	AND title = '"+title+"' " + 
        			"GROUP BY" + 
        			"	jbfy" + 
        			") t on sfzs_czrk.fybm = t.jbfy where sfzs_czrk.year = '"+date);
    		if(kind.equals("中")) {
        		sql.append("' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%0' AND sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' ");
        	}else if(kind.equals("辖")){
        		sql.append("' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%' AND substring(sfzs_czrk.fybm from 2 for 2)<>'00' AND substring(sfzs_czrk.fybm from 3 for 1)<>'0'");
        	}
        	sql.append(" ORDER BY yearzs,lastyearzs,fy)t1");
    	}else {
    		sql.append("select jbfy, fy,lastyearzs,\r\n" + 
        			"	hbzs,\r\n" + 
        			"	yearzs,\r\n" + 
        			"	(yearzs-lastyearzs) as cz,\r\n" + 
        			"	(yearzs-hbzs) as hbcz, rank() over(order by yearzs) as rank from(select sfzs_czrk.fybm as jbfy,replace(fyjc,'法院','') as fy,(CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,(CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,(CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs from sfzs_czrk left join " + 
        			"(SELECT" + 
        			"	jbfy," + 
        			"	Round(cast(SUM ( CASE WHEN sfzs_result_shzl.DATE = '"+(Integer.parseInt(date)-1)+"' and type='"+type+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS lastyearzs," + 
        			"	Round(cast(SUM ( CASE WHEN sfzs_result_shzl.DATE = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS hbzs," + 
        			"	Round(cast(SUM ( CASE WHEN sfzs_result_shzl.DATE = '"+date+"' and type='"+type+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS yearzs " + 
        			"FROM" + 
        			"	sfzs_result_shzl " + 
        			"WHERE" + 
        			"	jbfy LIKE'"+fybm.substring(0, 1)+"%' " + 
        			"	AND DATE IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) " + 
        			"	AND kind = '"+kind+"' " + 
        			"	AND title = '"+title+"' " + 
        			"GROUP BY" + 
        			"	jbfy" + 
        			") t on sfzs_czrk.fybm = t.jbfy where sfzs_czrk.year = '"+date);
        	if(kind.equals("中")) {
        		sql.append("' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%0' AND sfzs_czrk.fybm<>'"+fybm.substring(0, 1)+"'||'00' ");
        	}else if(kind.equals("辖")){
        		sql.append("' and sfzs_czrk.fybm LIKE'"+fybm.substring(0, 1)+"%' AND substring(sfzs_czrk.fybm from 2 for 2)<>'00' AND substring(sfzs_czrk.fybm from 3 for 1)<>'0'");
        	}
        	sql.append(" ORDER BY yearzs,lastyearzs,fy)t1");
    	}
    	Query query = entityManager.createNativeQuery(sql.toString());  
    	query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //同等规模城市排名表格
    public List getTdZjTableList(String type, String date, String fybm ,String title,int multiple){  
    	String sql = "";
    	if(type.equals("year")) {
    		sql = "SELECT jbfy," + 
        			"	fy,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	yearzs,\r\n" + 
        			"	cz,\r\n" + 
        			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(SELECT sfzs_czrk.fybm as jbfy," + 
        			"	REPLACE(fyjc,'法院','') AS fy,\r\n" + 
        			"	fybm,\r\n" + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN cz IS NULL THEN 0 ELSE cz END ) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_czrk\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	Round(cast(sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS lastyearzs,\r\n" + 
        			"	Round(cast(sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS yearzs,\r\n" + 
        			"	(\r\n" + 
        			"	Round(cast(sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) - Round(cast(sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) \r\n" + 
        			"	) AS cz \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_shzl,\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND sfzs_result_shzl.jbfy = sfzs_czrk.fybm \r\n" + 
        			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
        			"	AND type = '"+type+"' \r\n" + 
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) AND title = '"+title+"' GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,lastyearzs,\r\n" + 
        			"	fy)t1";
    	}else {
    		sql = "SELECT jbfy," + 
        			"	fy,\r\n" + 
        			"	lastyearzs,\r\n" + 
        			"	hbzs,\r\n" + 
        			"	yearzs,\r\n" + 
        			"	(yearzs-lastyearzs) as cz,\r\n" + 
        			"	(yearzs-hbzs) as hbcz,\r\n" +
        			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
        			"FROM\r\n" + 
        			"	(SELECT sfzs_czrk.fybm as jbfy," + 
        			"	REPLACE(fyjc,'法院','') AS fy,\r\n" + 
        			"	fybm,\r\n" + 
        			"	( CASE WHEN lastyearzs IS NULL THEN 0 ELSE lastyearzs END ) AS lastyearzs,\r\n" + 
        			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs,\r\n" + 
        			"	( CASE WHEN hbzs IS NULL THEN 0 ELSE hbzs END ) AS hbzs \r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_czrk\r\n" + 
        			"	LEFT JOIN (\r\n" + 
        			"SELECT\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc AS fy,\r\n" + 
        			"	Round(cast(sum( CASE WHEN sfzs_result_shzl.date = '"+(Integer.parseInt(date)-1)+"' and type='"+type+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS lastyearzs,\r\n" + 
        			"	Round(cast(sum( CASE WHEN sfzs_result_shzl.date = '"+NumUtil.getHalfYearReportDate(date, type)+"' and type='"+NumUtil.getHalfYearReportType(type)+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS hbzs,\r\n" + 
        			"	Round(cast(sum( CASE WHEN sfzs_result_shzl.date = '"+date+"' and type='"+type+"' THEN (cnum/pnum)*"+multiple+" ELSE 0 END ) as NUMERIC),2) AS yearzs\r\n" + 
        			"FROM\r\n" + 
        			"	sfzs_result_shzl,\r\n" + 
        			"	sfzs_czrk \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND sfzs_result_shzl.jbfy = sfzs_czrk.fybm \r\n" + 
        			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
        			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
        			"	AND (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) AND title = '"+title+"' GROUP BY\r\n" + 
        			"	jbfy,\r\n" + 
        			"	fyjc \r\n" + 
        			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
        			"WHERE\r\n" + 
        			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
        			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
        			"ORDER BY\r\n" + 
        			"	yearzs,lastyearzs,\r\n" + 
        			"	fy)t1";
    	}
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    //同等收案量排名表格
    public List getTdSalTableList(String type, String date, String fybm, String title, int multiple){  
    	StringBuffer sql = new StringBuffer("SELECT\r\n" + 
    			"	sal,\r\n" + 
    			"	fybm,\r\n" + 
    			"	fy,\r\n" + 
    			"	( CASE WHEN result IS NULL THEN 0 ELSE result END ) AS result \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	sal,\r\n" + 
    			"	fybm,\r\n" + 
    			"	REPLACE ( fyjc, '法院', '' ) || '法院' AS fy,\r\n" + 
    			"	ROUND( cast( ( cnum / pnum ) * "+multiple+" AS NUMERIC ), 2 ) AS result \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk\r\n" + 
    			"	LEFT JOIN sfzs_result_shzl ON sfzs_result_shzl.jbfy = sfzs_czrk.fybm \r\n" + 
    			"	AND sfzs_czrk.YEAR = sfzs_result_shzl.date \r\n" + 
    			"	AND kind = '中' \r\n" + 
    			"	AND type = '"+type+"' \r\n" + 
    			"	AND title = '"+title+"' \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND sal IN ( SELECT sal FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
    			"	) t \r\n" + 
    			"ORDER BY\r\n" + 
    			"	result");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    //中级法院基本信息
    public List getQxInfo(String date, String fybm ,String title, String type){  
    	String sql = "SELECT\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	count( * ) \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
    			"	) AS c,\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	fy,\r\n" + 
    			"	fybm,\r\n" + 
    			"	yearzs,\r\n" + 
    			"	rank ( ) over ( ORDER BY yearzs ) AS rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	fyjc AS fy,\r\n" + 
    			"	fybm,\r\n" + 
    			"	( CASE WHEN yearzs IS NULL THEN 0 ELSE yearzs END ) AS yearzs \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk\r\n" + 
    			"	LEFT JOIN (\r\n" + 
    			"SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fyjc AS fy,\r\n" + 
    			"	SUM( CASE WHEN sfzs_result_shzl.DATE = '"+date+"' THEN cnum/pnum ELSE 0 END ) AS yearzs \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_result_shzl,\r\n" + 
    			"	sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND sfzs_result_shzl.jbfy = sfzs_czrk.fybm \r\n" + 
    			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
    			"	AND type = '"+type+"' \r\n" + 
    			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
    			"	AND kind = '中' AND title='"+title+"'" + 
    			"GROUP BY\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fyjc \r\n" + 
    			"	) t ON sfzs_czrk.fybm = t.jbfy \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND size IN ( SELECT size FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm+"' ) \r\n" + 
    			"ORDER BY\r\n" + 
    			"	yearzs,\r\n" + 
    			"	fy \r\n" + 
    			"	) t1 \r\n" + 
    			"	) t2 \r\n" + 
    			"WHERE\r\n" + 
    			"	fybm = '"+fybm+"' \r\n" + 
    			"	) AS rank,\r\n" + 
    			"	( SELECT fyjc FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+date+"' AND fybm = '"+fybm.substring(0, 1)+"' || '00' ) AS xq,\r\n" + 
    			"	size \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_czrk.YEAR = '"+date+"' \r\n" + 
    			"	AND fybm = '"+fybm+"'";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //得到常驻人口数
    public String getRk(String date, String fybm, String type){ 
    	Query query = entityManager.createNativeQuery("select "+type+" as num from sfzs_czrk where year='"+date+"' and fybm ='"+fybm+"'");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        if(rows.size()>0) {
    		Map mapYear = (Map)rows.get(0);
    		if(!mapYear.get("num").toString().equals("null")) {
    			return mapYear.get("num").toString();
    		}		
    	}
        return "--";
    }
    
    //得到法院简称
    public String getFy(String fybm){ 
    	Query query = entityManager.createNativeQuery("select fyjc from sfzs_czrk where fybm ='"+fybm+"'");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        if(rows.size()>0) {
    		Map mapYear = (Map)rows.get(0);
    		if(!mapYear.get("fyjc").toString().equals("null")) {
    			return mapYear.get("fyjc").toString();
    		}		
    	}
        return "--";
    }
    
    //所属中院均值/所属高院均值
    /*public List getJz(String date, String fybm ,String title, String kind, String type ,int multiple){ 
    	StringBuffer sql = new StringBuffer();
    	sql.append("select Round(avg(lastyearzs),2) AS lastyearzs ,Round(avg(yearzs),2) AS yearzs,Round(avg(cz),2) AS cz  from(SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+(Integer.parseInt(date)-1)+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) AS lastyearzs,\r\n" + 
    			"	Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+date+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) AS yearzs,\r\n" + 
    			"	(\r\n" + 
    			"	Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+date+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) - Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+(Integer.parseInt(date)-1)+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) \r\n" + 
    			"	) AS cz " + 
    			"FROM\r\n" + 
    			"	sfzs_result_shzl \r\n" + 
    			"WHERE\r\n");
    	if(kind.equals("中")) {
    		sql.append("	jbfy LIKE '"+fybm.substring(0, 2)+"%' \r\n" );
    	}else if(kind.equals("高")) {
    		sql.append("	jbfy LIKE '"+fybm.substring(0, 1)+"%' \r\n" );
    	}
    	sql.append("	AND (substring(jbfy from 2 for 2)<>'00' AND substring(jbfy from 3 for 1)<>'0')\r\n" + 
    			"	AND TYPE = '"+type+"' \r\n" + 
    			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
    			"	AND kind = '辖' \r\n" + 
    			"	AND title = '"+title+"' GROUP BY\r\n" + 
    					"	jbfy) t ");
    	
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    public void getShzlzsGy(Map dataMap,String kind,String zsmc,String type, String date, String fybm,String fymc) {//法官工作量变化指数
    	getSectionOne(dataMap,kind,type,date,fybm,zsmc);
		String rankQG1_rankZJ1="";
		List listQG3 = new ArrayList();
		List qgTableList = new ArrayList();
		List qgSelfList = new ArrayList();
		List qgTableList3 = new ArrayList();
		if(zsmc.equals("fnet")||zsmc.equals("ypaq")||zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
			qgTableList = getQgTableList(type, date, zsmc, 100, false);
			qgSelfList = getQgTableList(type, date, zsmc, 100, true);
		}else {
			qgTableList = getQgTableList(type, date, zsmc, 1, false);
			qgSelfList = getQgTableList(type, date, zsmc, 1, true);
		}
		for(int i=qgTableList.size()-1;i>=0;i--) {//为全国报告增加
			Map row = (Map)qgTableList.get(i);
			String fy = row.get("fy").toString();
			if(fy.contains("北京")||fy.contains("天津")||fy.contains("上海")||fy.contains("重庆")) {
				continue;
			}else {
				dataMap.put("shzl_"+zsmc+"_rank1_qg",NumUtil.toSheng(fy));
				if(fymc.equals("全国")) {
					List zjList = new ArrayList();
					if(zsmc.equals("fnet")||zsmc.equals("ypaq")||zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
						zjList = getZjTableList(type, date, row.get("jbfy").toString(), zsmc, "中",100);
					}else {
						zjList = getZjTableList(type, date, row.get("jbfy").toString(), zsmc, "中",1);
					}
					Map rowzj = (Map)zjList.get(0);
					rankQG1_rankZJ1 = NumUtil.toSheng(fy);
					dataMap.put("shzl_"+zsmc+"_rank1_zj", rowzj.get("fy").toString());//中级排名第一
				}
				break;
			}
		}
		baseService.getGyDataIntoMap(dataMap, qgTableList, qgSelfList, qgTableList3, fymc, "shzl_"+zsmc);
		//处理表格
		StringBuffer rankZJ3 = new StringBuffer();// 全省前三
		StringBuffer rankZJH3 = new StringBuffer();// 全省后三
		List listZJ3 = new ArrayList();
		List zjTableList = new ArrayList();
		if (fybm.equals("100") || fybm.equals("200") || fybm.equals("M00") || fybm.equals("900")) {// 直辖市
			if(zsmc.equals("fnet")||zsmc.equals("ypaq")||zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
				zjTableList = getZjTableList(type, date, fybm, zsmc, "辖",100);
			}else {
				zjTableList = getZjTableList(type, date, fybm, zsmc, "辖",1);
			}
		} else {
			if(zsmc.equals("fnet")||zsmc.equals("ypaq")||zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
				zjTableList = getZjTableList(type, date, fybm, zsmc, "中",100);
			}else {
				zjTableList = getZjTableList(type, date, fybm, zsmc, "中",1);
			}
		}
		for (int i = 0; i < zjTableList.size(); i++) {
			Map row = (Map) zjTableList.get(i);
			row.put("id", i + 1);
			row.put("lastyearzs", NumUtil.toNxiaoshu(row.get("lastyearzs").toString(), 2));
			row.put("yearzs", NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2));
			row.put("cz", NumUtil.toNxiaoshu(row.get("cz").toString(), 2));
			int r = Integer.parseInt(row.get("rank").toString());
			if (r <= 3) {
				listZJ3.add(row);
			}
		}
		if(zjTableList.size()>3) {
			for(int i=1;i<4;i++) {
				Map row = (Map) zjTableList.get(zjTableList.size()-i);
				rankZJH3.append(row.get("fy").toString() + "（" + NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2) + "）");
				rankZJH3.append("、");
			}
		}
		if (listZJ3.size() > 3) {// 只取前三个
			listZJ3 = listZJ3.subList(0, 3);
		}
		for (int i = 0; i < listZJ3.size(); i++) {
			Map row = (Map) listZJ3.get(i);
			rankZJ3.append(row.get("fy").toString() + "（" + NumUtil.toNxiaoshu(row.get("yearzs").toString(), 2) + "）");
			rankZJ3.append("、");
		}
		if (fybm.equals("100") || fybm.equals("200") || fybm.equals("M00") || fybm.equals("900")) {// 直辖市
			dataMap.put("shzl_" + zsmc + "_rank3_zj", rankZJ3.length()==0?"":rankZJ3.substring(0, rankZJ3.length() - 1) + "等区县");
			dataMap.put("shzl_" + zsmc + "_rankh3_zj", rankZJH3.length()==0?"":rankZJH3.substring(0, rankZJH3.length() - 1) + "等区县");
		} else {
			dataMap.put("shzl_" + zsmc + "_rank3_zj", rankZJ3.length()==0?"":rankZJ3.substring(0, rankZJ3.length() - 1) + "等地");
			dataMap.put("shzl_" + zsmc + "_rankh3_zj", rankZJH3.length()==0?"":rankZJH3.substring(0, rankZJH3.length() - 1) + "等地");
		}
		List fbList = getFbZjList(type, date, fybm, zsmc);
		for (int i = 0; i < fbList.size(); i++) {
			Map row = (Map) fbList.get(i);
			row.put("id", i + 1);
		}
		if (fybm.equals("ZZ0")){
			if (zsmc.equals("mjjd")) {
				dataMap.put("array19", qgTableList);
				dataMap.put("array51", fbList);
				dataMap.put(39,rankQG1_rankZJ1);
			} else if (zsmc.equals("lwjf")) {
				dataMap.put("array20", qgTableList);
				dataMap.put("array52", fbList);
				dataMap.put(41,rankQG1_rankZJ1);
			} else if (zsmc.equals("jrzp")) {
				dataMap.put("array21", qgTableList);
				dataMap.put("array53", fbList);
				dataMap.put(43,rankQG1_rankZJ1);
			} else if (zsmc.equals("dljt")) {
				dataMap.put("array22", qgTableList);
				dataMap.put("array54", fbList);
				dataMap.put(45,rankQG1_rankZJ1);
			} else if (zsmc.equals("rsaq")) {
				dataMap.put("array23", qgTableList);
				dataMap.put("array55", fbList);
				dataMap.put(47,rankQG1_rankZJ1);
			} else if (zsmc.equals("wcnqy")) {
				dataMap.put("array24", qgTableList);
				dataMap.put("array56", fbList);
				dataMap.put(49,rankQG1_rankZJ1);
			} else if (zsmc.equals("wcnfz")) {
				dataMap.put("array25", qgTableList);
				dataMap.put("array57", fbList);
				dataMap.put(51,rankQG1_rankZJ1);
			} else if (zsmc.equals("fnet")) {
				dataMap.put("array26", qgTableList);
				dataMap.put("array58", fbList);
				dataMap.put(53,rankQG1_rankZJ1);
			} else if (zsmc.equals("hdd")) {
				dataMap.put("array27", qgTableList);
				dataMap.put("array59", fbList);
				dataMap.put(55,rankQG1_rankZJ1);
			} else if (zsmc.equals("spaq")) {
				dataMap.put("array28", qgTableList);
				dataMap.put("array60", fbList);
				dataMap.put(57,rankQG1_rankZJ1);
			} else if (zsmc.equals("ypaq")) {
				dataMap.put("array29", qgTableList);
				dataMap.put("array61", fbList);
				dataMap.put(59,rankQG1_rankZJ1);
			} else if (zsmc.equals("grcc")) {
				dataMap.put("array30", qgTableList);
				dataMap.put("array62", fbList);
				dataMap.put(61,rankQG1_rankZJ1);
			} else if (zsmc.equals("jtjf")) {
				dataMap.put("array31", qgTableList);
				dataMap.put("array63", fbList);
				dataMap.put(63,rankQG1_rankZJ1);
			} else if (zsmc.equals("lljf")) {
				dataMap.put("array32", qgTableList);
				dataMap.put("array64", fbList);
				dataMap.put(65,rankQG1_rankZJ1);
			} else if (zsmc.equals("lyjf")) {
				dataMap.put("array33", qgTableList);
				dataMap.put("array65", fbList);
				dataMap.put(67,rankQG1_rankZJ1);
			} else if (zsmc.equals("zrzy")) {
				dataMap.put("array34", qgTableList);
				dataMap.put("array66", fbList);
				dataMap.put(69,rankQG1_rankZJ1);
			} else if (zsmc.equals("sthj")) {
				dataMap.put("array35", qgTableList);
				dataMap.put("array67", fbList);
				dataMap.put(71,rankQG1_rankZJ1);
			}
		}else if (fybm.equals("100") || fybm.equals("200") || fybm.equals("M00") || fybm.equals("900")) {// 直辖市
			if (zsmc.equals("mjjd")) {
				dataMap.put("array1", qgTableList3);
				dataMap.put("array2", zjTableList);
				dataMap.put("array32", fbList);
			} else if (zsmc.equals("lwjf")) {
				dataMap.put("array3", qgTableList3);
				dataMap.put("array4", zjTableList);
				dataMap.put("array33", fbList);
			} else if (zsmc.equals("jrzp")) {
				dataMap.put("array5", qgTableList3);
			} else if (zsmc.equals("dljt")) {
				dataMap.put("array6", qgTableList3);
			} else if (zsmc.equals("rsaq")) {
				dataMap.put("array7", qgTableList3);
				dataMap.put("array8", zjTableList);
				dataMap.put("array34", fbList);
			} else if (zsmc.equals("wcnqy")) {
				dataMap.put("array9", qgTableList3);
				dataMap.put("array10", zjTableList);
				dataMap.put("array35", fbList);
			} else if (zsmc.equals("wcnfz")) {
				dataMap.put("array11", qgTableList3);
				dataMap.put("array12", zjTableList);
				dataMap.put("array36", fbList);
			} else if (zsmc.equals("fnet")) {
				dataMap.put("array13", qgTableList3);
				dataMap.put("array14", zjTableList);
				dataMap.put("array37", fbList);
			} else if (zsmc.equals("hdd")) {
				dataMap.put("array15", qgTableList3);
				dataMap.put("array16", zjTableList);
				dataMap.put("array38", fbList);
			} else if (zsmc.equals("spaq")) {
				dataMap.put("array17", qgTableList3);
				dataMap.put("array18", zjTableList);
				dataMap.put("array39", fbList);
			} else if (zsmc.equals("ypaq")) {
				dataMap.put("array19", qgTableList3);
				dataMap.put("array20", zjTableList);
				dataMap.put("array40", fbList);
			} else if (zsmc.equals("grcc")) {
				dataMap.put("array21", qgTableList3);
				dataMap.put("array22", zjTableList);
				dataMap.put("array41", fbList);
			} else if (zsmc.equals("jtjf")) {
				dataMap.put("array23", qgTableList3);
				dataMap.put("array24", zjTableList);
				dataMap.put("array42", fbList);
			} else if (zsmc.equals("lljf")) {
				dataMap.put("array25", qgTableList3);
				dataMap.put("array26", zjTableList);
				dataMap.put("array43", fbList);
			} else if (zsmc.equals("lyjf")) {
				dataMap.put("array27", qgTableList3);
			} else if (zsmc.equals("zrzy")) {
				dataMap.put("array28", qgTableList3);
				dataMap.put("array29", zjTableList);
				dataMap.put("array44", fbList);
			} else if (zsmc.equals("sthj")) {
				dataMap.put("array30", qgTableList3);
				dataMap.put("array31", zjTableList);
				dataMap.put("array45", fbList);
			}
		}else {
			if (zsmc.equals("mjjd")) {
				dataMap.put("array1", qgTableList3);
				dataMap.put("array2", zjTableList);
				dataMap.put("array35", fbList);
			} else if (zsmc.equals("lwjf")) {
				dataMap.put("array3", qgTableList3);
				dataMap.put("array4", zjTableList);
				dataMap.put("array36", fbList);
			} else if (zsmc.equals("jrzp")) {
				dataMap.put("array5", qgTableList3);
				dataMap.put("array6", zjTableList);
				dataMap.put("array37", fbList);
			} else if (zsmc.equals("dljt")) {
				dataMap.put("array7", qgTableList3);
				dataMap.put("array8", zjTableList);
				dataMap.put("array38", fbList);
			} else if (zsmc.equals("rsaq")) {
				dataMap.put("array9", qgTableList3);
				dataMap.put("array10", zjTableList);
				dataMap.put("array39", fbList);
			} else if (zsmc.equals("wcnqy")) {
				dataMap.put("array11", qgTableList3);
				dataMap.put("array12", zjTableList);
				dataMap.put("array40", fbList);
			} else if (zsmc.equals("wcnfz")) {
				dataMap.put("array13", qgTableList3);
				dataMap.put("array14", zjTableList);
				dataMap.put("array41", fbList);
			} else if (zsmc.equals("fnet")) {
				dataMap.put("array15", qgTableList3);
				dataMap.put("array16", zjTableList);
				dataMap.put("array42", fbList);
			} else if (zsmc.equals("hdd")) {
				dataMap.put("array17", qgTableList3);
				dataMap.put("array18", zjTableList);
				dataMap.put("array43", fbList);
			} else if (zsmc.equals("spaq")) {
				dataMap.put("array19", qgTableList3);
				dataMap.put("array20", zjTableList);
				dataMap.put("array44", fbList);
			} else if (zsmc.equals("ypaq")) {
				dataMap.put("array21", qgTableList3);
				dataMap.put("array22", zjTableList);
				dataMap.put("array45", fbList);
			} else if (zsmc.equals("grcc")) {
				dataMap.put("array23", qgTableList3);
				dataMap.put("array24", zjTableList);
				dataMap.put("array46", fbList);
			} else if (zsmc.equals("jtjf")) {
				dataMap.put("array25", qgTableList3);
				dataMap.put("array26", zjTableList);
				dataMap.put("array47", fbList);
			} else if (zsmc.equals("lljf")) {
				dataMap.put("array27", qgTableList3);
				dataMap.put("array28", zjTableList);
				dataMap.put("array48", fbList);
			} else if (zsmc.equals("lyjf")) {
				dataMap.put("array29", qgTableList3);
				dataMap.put("array30", zjTableList);
				dataMap.put("array49", fbList);
			} else if (zsmc.equals("zrzy")) {
				dataMap.put("array31", qgTableList3);
				dataMap.put("array32", zjTableList);
				dataMap.put("array50", fbList);
			} else if (zsmc.equals("sthj")) {
				dataMap.put("array33", qgTableList3);
				dataMap.put("array34", zjTableList);
				dataMap.put("array51", fbList);
			}
		}
		
    } 
    public void getShzlzsZy(Map dataMap,String kind,String zsmc,String type, String date, String fybm,String fymc) {
    	getSectionOne(dataMap,kind,type,date,fybm,zsmc);
		List zjTableList = new ArrayList();
		if (zsmc.equals("fnet") || zsmc.equals("ypaq") || zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
			zjTableList = getZjTableList(type, date, fybm, zsmc,"中",100);
		}else {
			zjTableList = getZjTableList(type, date, fybm, zsmc,"中",1);
		}
		baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "shzl_"+zsmc);
		
		
		List infoList = getQxInfo(date, fybm, zsmc, type);
		List tdTableList = new ArrayList();
		if (zsmc.equals("fnet") || zsmc.equals("ypaq") || zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
			tdTableList = getTdZjTableList(type, date, fybm, zsmc, 100);
		}else {
			tdTableList = getTdZjTableList(type, date, fybm, zsmc, 1);
		}
		baseService.getTdCityDataIntoMap(dataMap, infoList, tdTableList, "shzl_"+zsmc,fybm);
		
		//同等收案量排行
		List tdSalTableList = new ArrayList();
		if (zsmc.equals("fnet") || zsmc.equals("ypaq") || zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
			tdSalTableList = getTdSalTableList(type, date, fybm, zsmc, 100);
		}else {
			tdSalTableList = getTdSalTableList(type, date, fybm, zsmc, 1);
		}
		baseService.getTdSalDataIntoMap(dataMap, tdSalTableList, fymc,"shzl_"+zsmc);
		
		//处理表格
		if (zsmc.equals("mjjd")) {
			dataMap.put("array1", zjTableList);
			dataMap.put("array2", tdTableList);
		} else if (zsmc.equals("lwjf")) {
			dataMap.put("array3", zjTableList);
			dataMap.put("array4", tdTableList);
		} else if(zsmc.equals("jrzp")) {
			dataMap.put("array5", zjTableList);
			dataMap.put("array6", tdTableList);
		} else if(zsmc.equals("dljt")) {
			dataMap.put("array7", zjTableList);
			dataMap.put("array8", tdTableList);
		} else if (zsmc.equals("rsaq")) {
			dataMap.put("array9", zjTableList);
			dataMap.put("array10", tdTableList);
		} else if (zsmc.equals("wcnqy")) {
			dataMap.put("array11", zjTableList);
			dataMap.put("array12", tdTableList);
		} else if (zsmc.equals("wcnfz")) {
			dataMap.put("array13", zjTableList);
			dataMap.put("array14", tdTableList);
		} else if (zsmc.equals("fnet")) {
			dataMap.put("array15", zjTableList);
			dataMap.put("array16", tdTableList);
		} else if (zsmc.equals("hdd")) {
			dataMap.put("array17", zjTableList);
			dataMap.put("array18", tdTableList);
		} else if (zsmc.equals("spaq")) {
			dataMap.put("array19", zjTableList);
			dataMap.put("array20", tdTableList);
		} else if (zsmc.equals("ypaq")) {
			dataMap.put("array21", zjTableList);
			dataMap.put("array22", tdTableList);
		} else if (zsmc.equals("grcc")) {
			dataMap.put("array23", zjTableList);
			dataMap.put("array24", tdTableList);
		} else if (zsmc.equals("jtjf")) {
			dataMap.put("array25", zjTableList);
			dataMap.put("array26", tdTableList);
		} else if (zsmc.equals("lljf")) {
			dataMap.put("array27", zjTableList);
			dataMap.put("array28", tdTableList);
		} else if (zsmc.equals("lyjf")) {
			dataMap.put("array29", zjTableList);
			dataMap.put("array30", tdTableList);
		} else if (zsmc.equals("zrzy")) {
			dataMap.put("array31", zjTableList);
			dataMap.put("array32", tdTableList);
		} else if (zsmc.equals("sthj")) {
			dataMap.put("array33", zjTableList);
			dataMap.put("array34", tdTableList);
		}
    } 
    
    
    public List getZyOrGy(String table, String kind, String type, String date, String fybm, int multiple) {
    	StringBuffer sql = new StringBuffer();
    	sql.append("SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+(Integer.parseInt(date)-1)+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) AS lastyearzs,\r\n" + 
    			"	Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+date+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) AS yearzs,\r\n" + 
    			"	(\r\n" + 
    			"	Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+date+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) - Round(\r\n" + 
    			"	cast( SUM( CASE WHEN sfzs_result_shzl.DATE = '"+(Integer.parseInt(date)-1)+"' THEN ( cnum / pnum ) * "+multiple+" ELSE 0 END ) AS NUMERIC ),\r\n" + 
    			"	2 \r\n" + 
    			"	) \r\n" + 
    			"	) AS cz " + 
    			"FROM\r\n" + 
    			"	sfzs_result_shzl \r\n" + 
    			"WHERE\r\n");
    	if(kind.equals("中")) {
    		sql.append("	jbfy ='"+fybm.substring(0, 2)+"'||'0' \r\n" );
    	}else if(kind.equals("高")) {
    		sql.append("	jbfy ='"+fybm.substring(0, 1)+"'||'00' \r\n" );
    	}
    	sql.append("	AND TYPE = '"+type+"' \r\n" + 
    			"	AND date IN ( '"+date+"', '"+(Integer.parseInt(date)-1)+"' ) \r\n" + 
    			"	AND kind = '"+kind+"' \r\n" + 
    			"	AND title = '"+table+"' GROUP BY\r\n" + 
    					"	jbfy");
    	
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    
    
    @Autowired 
	TFypzService tFypzService; 

	public void getShzlzsQx(Map dataMap, String kind, String zsmc, String type, String date, String fybm) {// 区县法院指数
		String ssxq_zy =  ((Object[])tFypzService.getParentFymc(fybm,"zhongji").get(0))[1].toString();
		String ssxq_gy =  ((Object[])tFypzService.getParentFymc(fybm,"gaoji").get(0))[1].toString();
		Map zy = getSectionOne(dataMap, "中", type, date, ssxq_zy, zsmc);
		Map gy = getSectionOne(dataMap, "高", type, date, ssxq_gy, zsmc);
		Map self = getSectionOne(dataMap, kind, type, date, fybm, zsmc);
		getQxDataIntoMap(dataMap, zy, zsmc,"zy",NumUtil.toNxiaoshu(self.get("yearzs")==null?"":self.get("yearzs").toString(), 2));
		getQxDataIntoMap(dataMap, gy, zsmc,"gy",NumUtil.toNxiaoshu(self.get("yearzs")==null?"":self.get("yearzs").toString(), 2));
		List list = new ArrayList();
		list.add(gy);
		list.add(zy);
		list.add(self);
		for (int i = 0; i < list.size(); i++) {
			Map row = (Map) list.get(i);
			row.put("id", i + 1);
		}
		// 处理表格
		if (zsmc.equals("mjjd")) {
			dataMap.put("array1", list);
		} else if (zsmc.equals("lwjf")) {
			dataMap.put("array2", list);
		} else if (zsmc.equals("jrzp")) {
			dataMap.put("array3", list);
		} else if (zsmc.equals("rsaq")) {
			dataMap.put("array4", list);
		} else if (zsmc.equals("wcnqy")) {
			dataMap.put("array5", list);
		} else if (zsmc.equals("wcnfz")) {
			dataMap.put("array6", list);
		} else if (zsmc.equals("fnet")) {
			dataMap.put("array7", list);
		} else if (zsmc.equals("hdd")) {
			dataMap.put("array8", list);
		} else if (zsmc.equals("spaq")) {
			dataMap.put("array9", list);
		} else if (zsmc.equals("ypaq")) {
			dataMap.put("array10", list);
		} else if (zsmc.equals("grcc")) {
			dataMap.put("array11", list);
		} else if (zsmc.equals("jtjf")) {
			dataMap.put("array12", list);
		} else if (zsmc.equals("lljf")) {
			dataMap.put("array13", list);
		} else if (zsmc.equals("zrzy")) {
			dataMap.put("array14", list);
		} else if (zsmc.equals("sthj")) {
			dataMap.put("array15", list);
		}

	}
    /*public void getQxZs(Map dataMap, String kind, String zsmc, String type, String date, String fybm) {//区县法院指数
    	Map self = getSectionOne(dataMap,kind,type,date,fybm,zsmc);
    	List listSszy = new ArrayList();
    	if (zsmc.equals("fnet") || zsmc.equals("ypaq") || zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
    		listSszy = getJz(date, fybm, zsmc, "中", type, 100);
		}else {
			listSszy = getJz(date, fybm, zsmc, "中", type, 1);
		}
    	
    	List listSsgy = new ArrayList();
    	if (zsmc.equals("fnet") || zsmc.equals("ypaq") || zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
    		listSsgy = getJz(date, fybm, zsmc, "高", type, 100);
		}else {
			listSsgy = getJz(date, fybm, zsmc, "高", type, 1);
		}	
    	List xqlist = new ArrayList();
    	getJzIntoMap(dataMap, listSszy, fybm,zsmc,"zy",Double.parseDouble(self.get("yearzs").toString()),xqlist);
    	getJzIntoMap(dataMap, listSszy, fybm,zsmc,"gy",Double.parseDouble(self.get("yearzs").toString()),xqlist);
    	xqlist.add(self);
    	
    	dataMap.put("array32",self); 
    }*/
    
    public static void getQxDataIntoMap(Map dataMap, Map map, String zsmc,String zyOrGy,double yearzs) {
    	double jz = NumUtil.toNxiaoshu(map.get("yearzs")==null?"":map.get("yearzs").toString(), 2);
		dataMap.put("shzl_"+zsmc+"_jz_"+zyOrGy, jz);
		double jzcz = NumUtil.toNxiaoshu(yearzs - jz,2);
		if(jzcz>0){
			dataMap.put("shzl_"+zsmc+"_jz_"+zyOrGy+"_cz", "高"+Math.abs(jzcz));
		}else if(jzcz<0){
			dataMap.put("shzl_"+zsmc+"_jz_"+zyOrGy+"_cz", "低"+Math.abs(jzcz));
		}else {
			dataMap.put("shzl_"+zsmc+"_jz_"+zyOrGy+"_cz", "相等");
		}
    }
      
    public Map getSectionOne(Map dataMap,String kind,String type, String date, String fybm, String zsmc) {
    	List gsYearlist =  new ArrayList();
    	List gsLastYearlist =  new ArrayList();
    	List hblist =  new ArrayList();
    	if (zsmc.equals("fnet") || zsmc.equals("ypaq") || zsmc.equals("spaq") || zsmc.equals("jrzp") || zsmc.equals("lyjf") || zsmc.equals("zrzy") || zsmc.equals("sthj")) {
    		gsYearlist = getGsList(kind,type,date,fybm,zsmc,100);//获取指数概况
        	gsLastYearlist = getGsList(kind,type,(Integer.parseInt(date)-1)+"",fybm,zsmc,100);//获取指数概况
        	hblist = getGsList(kind,NumUtil.getHalfYearReportType(type),NumUtil.getHalfYearReportDate(date, type)+"",fybm,zsmc,100);
    	}else {
    		gsYearlist = getGsList(kind,type,date,fybm,zsmc,1);//获取指数概况
        	gsLastYearlist = getGsList(kind,type,(Integer.parseInt(date)-1)+"",fybm,zsmc,1);//获取指数概况
        	hblist = getGsList(kind,NumUtil.getHalfYearReportType(type),NumUtil.getHalfYearReportDate(date, type)+"",fybm,zsmc,1);
    	}  	
    	Map mapYear = new HashMap();
    	Map mapLastYear = new HashMap();
    	Map mapHb = new HashMap();
    	int aj = 0;
    	String rk = "--";
    	double zs = 0;
    	double zsLastYear = 0;
    	double hbzs = 0;
    	String fy = "--";
    	if(gsYearlist.size()>0) {
    		mapYear = (Map)gsYearlist.get(0);
    		aj = Integer.parseInt(mapYear.get("cnum").toString());//案件数
        	rk = mapYear.get("pnum").toString();//常驻人口数
        	zs = NumUtil.toNxiaoshu(mapYear.get("result")!=null?mapYear.get("result").toString():"0", 2);
        	fy = mapYear.get("fy").toString();
    	}else {
    		if(zsmc.equals("jrzp")) {
    			rk = getRk(date,fybm,"num_jrtl");
    		}else if(zsmc.equals("dljt")) {
    			rk = getRk(date,fybm,"num_myqc");
    		}else if(zsmc.equals("lyjf")) {
    			rk = getRk(date,fybm,"num_lyrk");
    		}else {
    			rk = getRk(date,fybm,"num");
    		}
    		fy = getFy(fybm);
    	}
    	if(gsLastYearlist.size()>0) {
        	mapLastYear = (Map)gsLastYearlist.get(0);
        	zsLastYear = NumUtil.toNxiaoshu((mapLastYear.get("result")!=null?mapLastYear.get("result").toString():"0"), 2); 	
        	
    	} 
    	if(hblist.size()>0) {
    		mapHb = (Map)hblist.get(0);
        	hbzs = NumUtil.toNxiaoshu((mapHb.get("result")!=null?mapHb.get("result").toString():"0"), 2); 	
        	
    	} 
    	dataMap.put("shzl_"+zsmc+"zs", zs);
		dataMap.put("shzl_"+zsmc+"zs_lastyear", zsLastYear);
		
		dataMap.put("shzl_"+zsmc+"_aj", NumUtil.toWan(aj));
		dataMap.put("shzl_"+zsmc+"_rk", NumUtil.toNxiaoshu(rk,2));
		
		if(zs>zsLastYear) {
			dataMap.put("shzl_"+zsmc+"_tb", "上升"+(NumUtil.toNxiaoshu(zs-zsLastYear,2)));//同比
		}else if(zs<zsLastYear) {
			dataMap.put("shzl_"+zsmc+"_tb", "下降"+(NumUtil.toNxiaoshu(zsLastYear-zs,2)));//同比
		}else {
			dataMap.put("shzl_"+zsmc+"_tb", "不变");//同比
		}
		Map map = new HashMap();
		map.put("fy", fy.replace("法院", "").replace("人民法院", ""));
		map.put("lastyearzs", zsLastYear);
		map.put("yearzs", zs);
		map.put("hbzs", hbzs);
		map.put("cz", NumUtil.toNxiaoshu(zs - zsLastYear, 2));
		map.put("hbcz", NumUtil.toNxiaoshu(zs - hbzs, 2));
		dataMap.put("zyxq", map);
		return map;
    }
}
